#!/usr/bin/env python
# -*- encoding: utf-8 -*-

import pandas as pd
import sys, datetime, json, logging
from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob


class CheckJob(AbstractDPJob):
    """
    通过两个SQL去取来源表和目标表的表结构

    传入6个参数   来源标  和目标表  及开始时间结束时间

    将来源表那段时间的数据取出来  在存到目标表里
    """
    p_db_conn_gxzl = None
    def __init__(self,
                 p_config=None,
                 p_db_conn_gxzl=None):


        super(CheckJob, self).__init__(p_config=p_config,
                                        p_db_conn_mpp=None,
                                        p_db_conn_rds=None,
                                        p_db_conn_dbprod7=None,
                                        p_unit=None,
                                        p_account=None,
                                        p_cost_center=None,
                                        p_account_period_start=None,
                                        p_account_period_end=None)
        self.db_conn_gxzl = p_db_conn_gxzl

        pass

    def do_execute(self):
        """
        """
        self.logger.info('CheckJob.do_execute')

        sql = " select UNITNO,DESCRIPTION,SUM(COUNT_NUM) as SUM_NUM " \
              " from( " \
              " select A.STARTTIME,A.MAT_NO,A.UNITNO,A.TABLE_ID,B.TABLE_ENAME,B.DESCRIPTION,B.TJ_EO_FLAG,1 as COUNT_NUM" \
              " from" \
              " (select STARTTIME,MAT_NO,UNITNO,TABLE_ID" \
              " from GXZL.QCFX00262 " \
              " where STARTTIME=TO_CHAR(CURRENT TIMESTAMP - 1 DAY, 'YYYYMMDD')" \
              " )as A left join  " \
              " (select TABLE_ID,TABLE_ENAME,DESCRIPTION,TJ_EO_FLAG from GXZL.SA_GX_J001)as B" \
              " on A.TABLE_ID=B.TABLE_ID " \
              " order by UNITNO,DESCRIPTION" \
              " )T group by UNITNO,DESCRIPTION"
        self.logger.info(sql)

        df1 = XRetryableQuery(p_db_conn=self.db_conn_gxzl, p_sql=sql, p_max_times=5).redo()
        success = df1.empty is False
        if success is False:
            return
        df1.columns = df1.columns.str.upper()







        sql = " select *from ( " \
              " select  COUNT,MAT_NO ,UNITNO,DESCRIPTION,TJ_EO_FLAG,row_number() over(partition by UNITNO,DESCRIPTION order by COUNT desc) as rowIndex" \
              " from (" \
              " select count(*) as COUNT,MAT_NO ,UNITNO,DESCRIPTION,TJ_EO_FLAG" \
              " from (" \
              " select a.STARTTIME,a.MAT_NO,a.UNITNO,a.TABLE_ID," \
              " b.TABLE_ENAME,b.DESCRIPTION,b.TJ_EO_FLAG" \
              " from GXZL.QCFX00262 a,GXZL.SA_GX_J001 b " \
              " where A.TABLE_ID=B.TABLE_ID and  a.STARTTIME=TO_CHAR(CURRENT TIMESTAMP - 1 DAY, 'YYYYMMDD')" \
              " ) group by " \
              " UNITNO,DESCRIPTION,MAT_NO,TJ_EO_FLAG)" \
              " ) where rowIndex<=5"
        self.logger.info(sql)

        df2 = XRetryableQuery(p_db_conn=self.db_conn_gxzl, p_sql=sql, p_max_times=5).redo()
        success = df2.empty is False
        if success is False:
            return
        df2.columns = df2.columns.str.upper()
        start = datetime.datetime.now()
        log = '11月2日点检情况：'












        super(CheckJob, self).do_execute()
